package com.manager.service.message;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.jorm.Session;

import com.build.SessionBuilder; 
import com.web.model.MessageBack;
import com.web.model.dao.MessageBackDao;
import com.web.model.dao.impl.MessageBackDaoImpl;
import com.web.model.vo.query.MessageBackQuery;
import com.web.tag.bean.PageModel;

public class MessageBackService {

	public List<MessageBack> findList(PageModel model, MessageBackQuery query) {
		List<MessageBack> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			MessageBackDao messageDao = new MessageBackDaoImpl();
			String whereSql = " where 1=1 ";
			if (query.getId() != null && query.getId() > 0) {
				whereSql += " and " + MessageBack.COLUMN_MID + " = "
						+ query.getMid();
			}
			if (query.getTitle() != null && !query.getTitle().trim().isEmpty()) {
				whereSql += " and " + MessageBack.COLUMN_TITLE + " like '%"
						+ query.getTitle() + "%'";
			}
			String sql = "select " + MessageBack.COLUMN_ADMIN_ID + ","
					+ MessageBack.COLUMN_CONTENT + "," + MessageBack.COLUMN_MID
					+ "," + MessageBack.COLUMN_STATE + ","
					+ MessageBack.COLUMN_TITLE + " ,"
					+ MessageBack.COLUMN_CREATE_TIME + ","
					+ MessageBack.COLUMN_ID + "  from "
					+ MessageBack.TABLE_NAME + " " + whereSql + " order by "
					+ MessageBack.COLUMN_CREATE_TIME + " desc";
			// System.out.println(sql);
			if (model != null) {
				try {
					model.setRecodeZong(messageDao.getResultCount(
							"select count(*) as ct from "
									+ MessageBack.TABLE_NAME + " " + whereSql,
							1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from ("
						+ sql + ") row_ where rownum <= " + row_x
						+ ") where rownum_ > " + row_y;
			}

			list =getList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<MessageBack> getList(String sql) {

		List list = new ArrayList();
		MessageBack obj = new MessageBack();
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				obj = new MessageBack();
				obj.setCreateTime(rs.getTimestamp(MessageBack.COLUMN_CREATE_TIME));
				obj.setMid(rs.getInt(MessageBack.COLUMN_MID));
				obj.setContent(rs.getString(MessageBack.COLUMN_CONTENT));
				obj.setState(rs.getInt(MessageBack.COLUMN_STATE));
				obj.setTitle(rs.getString(MessageBack.COLUMN_TITLE));
				obj.setAdminId(rs.getInt(MessageBack.COLUMN_ADMIN_ID)); 
				obj.setId(rs.getInt(MessageBack.COLUMN_ID));
				list.add(obj);
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<MessageBack> findList() {
		List<MessageBack> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			MessageBackDao messageDao = new MessageBackDaoImpl();
			String sql = "select  * from " + MessageBack.TABLE_NAME
					+ " order by " + MessageBack.COLUMN_CREATE_TIME + " ";
			list = messageDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<MessageBack> findListByMsg(int msgId) {
		List<MessageBack> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			MessageBackDao messageDao = new MessageBackDaoImpl();
			String sql = "select  * from " + MessageBack.TABLE_NAME + " where "
					+ MessageBack.COLUMN_MID + "=" + msgId + "   order by "
					+ MessageBack.COLUMN_CREATE_TIME + " ";
			list = messageDao.queryForList(sql);

			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public int save(MessageBack message) {
		MessageBackDao messageDao = new MessageBackDaoImpl();
		if (message.getId() == null || message.getId() <= 0) {
			messageDao.save(message);
		} else {
			messageDao.update(message);
		}
		return messageDao.save(message);

	}

	public MessageBack getById(int id) {
		MessageBack obj = new MessageBack();
		try {
			Session session = SessionBuilder.oracleSession();
			Connection conn = session.connection();
			String sql = "select * from " + MessageBack.TABLE_NAME + " where "
					+ MessageBack.COLUMN_ID + " = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				obj = new MessageBack();
				obj.setCreateTime(rs
						.getTimestamp(MessageBack.COLUMN_CREATE_TIME));
				obj.setMid(rs.getInt(MessageBack.COLUMN_MID));
				obj.setContent(rs.getString(MessageBack.COLUMN_CONTENT));
				obj.setState(rs.getInt(MessageBack.COLUMN_STATE));
				obj.setTitle(rs.getString(MessageBack.COLUMN_TITLE));
				obj.setAdminId(rs.getInt(MessageBack.COLUMN_ADMIN_ID));
				obj.setId(rs.getInt(MessageBack.COLUMN_ID));
			}
			rs.close();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}

	public void delete(int id) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " + MessageBack.TABLE_NAME + " where "
					+ MessageBack.COLUMN_ID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
